<?php

class Admin_AlunoController extends App_Controller_Action
{
    private $_table     = 'ALUNO';
    private $_pk        = 'ALU_ID';
    private $_order_col = 'ALU_NOM';
    private $_order     = 'ASC';
    private $_sta_col   = 'ALU_STA';
    private $obj        = '';
    private $form       = '';
    public  $dir        = 'public/upload/aluno/';
    public  $_file_col  = 'ALU_FILE';
    
    public function init()
    {
        Zend_Loader::loadClass('aluno','library');
        
        $this->obj = new Admin_Model_DbTable_Aluno();
        if($this->getRequest()->getActionName() == 'edit') $this->form = new Admin_Form_Aluno();
        $this->view->dir = $this->dir;
        $this->view->urlfoto = $this->_urlbase.'/'.$this->dir;
        
        if($this->getRequest()->getActionName() != 'index'){
            
            if(count($sessao->Menualuno) > 0){
                //$menu = $sessao->Menualuno;
            } else {
                $db = Zend_Db_Table_Abstract::getDefaultAdapter(); 
                
                $sessao = new Zend_Session_Namespace('Menualuno');
                $query = 'SELECT SQL_CACHE * FROM MENU_ALUNO WHERE MNA_STA = 1 ORDER BY MNA_ORD ASC';
                $menu = $db->fetchAll($query);
                $sessao->Menualuno = $menu;

                $sessao2 = new Zend_Session_Namespace('Menualuno_nom');
                foreach($menu as $men) $sessao2->Menualuno_nom[$men['MNA_ARQ']] = $men['MNA_NOM'];
            }
            
            //$sessao = new Zend_Session_Namespace('Menualuno_nom');
            $this->view->Menualuno_nom = $sessao2->Menualuno_nom;
        }        
    }

    public function indexAction() {
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('dominio','library');
        Zend_Loader::loadClass('elemform','library');
        //Zend_Loader::loadClass('aluno','library');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_list.js');
        
        $this->view->db = $this->db;
        
        $where = 'a.'.$this->_pk . ' > 0';
       
        $STA = $this->_getParam($this->_sta_col,'');
        if($STA != '') $where .= ' a.' . $this->_sta_col . ' = ' . $STA; 
        
        //$where_join = ' and CURSO.CUR_ID > 0';
        
        $itfiltro = $this->_getParam('itfiltro', ''); 
        $filtro_nm = $this->_getParam('filtro_nm', '');
        
        if($itfiltro != '') $where .= ' and CURSO.CUR_ID = ' . $itfiltro;
        if($filtro_nm != '') $where .= ' and a.ALU_NOM like "%' . trim($filtro_nm).'%"';
        
        $this->view->itfiltro = $itfiltro;
        $this->view->filtro_nm = $filtro_nm;
        
        
        $atrr = $this->_getParam('atrr', ''); 
        $ord = $this->_getParam('ord', ''); 
        
        $order = ' a.'.$this->_order_col.' '.$this->_order;
        if($atrr > '' && $ord > '') $order = $atrr.' '.$ord; 
        
        // .' m.MAT_STA, m.CUR_ID '
        
        $query = ' SELECT SQL_CACHE DISTINCT(a.ALU_ID), a.ALU_NOM, a.ALU_STA '
                .' FROM ALUNO a '
                .' LEFT JOIN MATRICULA m ON m.ALU_ID = a.ALU_ID AND MAT_STA = 1 '
                .' LEFT JOIN CURSO ON CURSO.CUR_ID = m.CUR_ID AND CUR_STA = 1 '
                .' WHERE '.$where.' ORDER BY '.$order;
        $result = $this->db->fetchAll($query);
        $this->view->result = $this->paginacao($result);
        
    }
    
    public function editAction() {
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskMoney.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskedinput.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/mask.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno.js?2=30'); 
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('imagem','library');
        Zend_Loader::loadClass('dominio','library');
        Zend_Loader::loadClass('elemform','library');
        //Zend_Loader::loadClass('aluno','library');
        
        $prf = new Admin_Model_DbTable_Profissao();
        $this->view->prf = $prf->fetchAll();
        
        //$this->view->form = $this->form;
        if($this->getRequest()->isPost()) {
            $formData = $this->getRequest()->getPost();
            try { 
                $this->db->beginTransaction();
                $formData['ALU_CPF'].' '.$formData[$this->_pk];
                $cpf = $this->verificarCPF($formData['ALU_CPF'],$formData[$this->_pk]);
                
                if($cpf == FALSE){ //($this->form->isValid($formData)) {
                    
                    $id = (int) $formData[$this->_pk];//$this->form->getValue($this->_pk);
                    
                    //$returnImg = imagem::upload($this->_file_col,$this->dir,$id.'_det');
                    
                    $data = $formData; //$this->form->getValues();
                    
                    unset($data['submit']);
                    
                    $data['ALU_DATNAS'] = Datetools::dma_amd($data['ALU_DATNAS']);
                    $data['ALU_PAIDATNAS'] = Datetools::dma_amd($data['ALU_PAIDATNAS']);
                    $data['ALU_MAEDATNAS'] = Datetools::dma_amd($data['ALU_MAEDATNAS']);
                    
                    unset($data['ALU_LOG']);
                    unset($data['ALU_SNH']);
                    unset($data['ALU_SNH2']);
                    unset($data[$this->_file_col]);
                    
                    if ($id == 0){
                        $login = $this->gerarlogin($formData['ALU_NOM']);
                        
                        // inserir usuário para aluno
                        $user = array(
                            'USU_LOGIN'     => $login, // $formData['ALU_LOG'],	
                            //'USU_SENHA'     => sha1($form->getValue('USU_SENHA')),	
                            'USU_NOME'      => $formData['ALU_NOM'],	
                            'PER_ID'        => 5, // permissão alunos
                            'USU_DTCADASTRO'=> date('Y-m-d'),
                            'USU_DATCAD'    => date('Y-m-d'),
                            'USU_TPO'       => 2, // 2 = alunos
                            'USU_CADSTA'    => 2,
                            'USU_STA'       => 1,  
                            'USU_EMAIL'     => $data['ALU_EML'],
                            'TUS_ID'        => 2,
                         );
                     
                        if(strlen($formData['ALU_SNH']) > 1){
                            $user['USU_SENHA'] = sha1($formData['ALU_SNH']);
                        }
                        
                        $obj = new Admin_Model_DbTable_Usuarios();
                        $obj->insert($user);
                        $USU_ID =  $obj->getAdapter()->lastInsertId();
                        
                        $data['USU_ID'] = $USU_ID;
                        $data['ALU_STA'] = 1;
                        $this->obj->insert($data);
                        $this->db->commit();
                        $id = $this->obj->getAdapter()->lastInsertId();
                        
                        //imagem::rename($id.'_det',$this->dir,$returnImg['filenew']);
                        
                        //$imgs = $this->dir.$id.'_det.*';
                        //foreach (glob($imgs) as $src) {
                        //    $img = $src;
                        //    imagem::reize($img, str_replace('det','tmb',$img), '80', '95');
                        //}
                        
                        $this->_helper->redirector('index');
                        
                    }
                    else{ 
                        // validar login
                        $ok = TRUE;
                        
                        $query = 'SELECT SQL_CACHE USU_ID FROM ALUNO WHERE ALU_ID = '.$id;
                        $row = $this->db->fetchRow($query);
                        
                        /*
                        $obj = new Admin_Model_DbTable_Usuarios();
                        $where = 'USU_ID <> '.$row->USU_ID.' and USU_LOGIN like "'.$formData['ALU_LOG'].'"';
                        $res = $obj->fetchRow($where);
                        if($res->USU_LOGIN == $formData['ALU_LOG']){
                            $ok = FALSE;
                            $msg = 'Login já cadastrado';
                        }
                        else{
                            $user = array(
                                'USU_LOGIN' => $formData['ALU_LOG'],	
                            );
                            
                            $obj->update($user, 'USU_ID = ' . $row->USU_ID);
                        }
                        */ 
                        
                        $query = 'SELECT SQL_CACHE USU_NOME FROM USUARIOS WHERE USU_ID = "'.$row['USU_ID'].'"';
                        $usu = $this->db->fetchAll($query);
                        if($usu['USU_NOME'] != $formData['ALU_NOM']){
                            $user = array(
                                'USU_NOME'  => $formData['ALU_NOM'],
                                'USU_EMAIL' => $formData['ALU_EML'],
                            );
                            
                            $this->db->update('USUARIOS',$user, 'USU_ID = ' . $row['USU_ID']);
                        } 
                        
                        if($ok){ 
                            
                            //$imgs = $this->dir.$id.'_det.*';
                            //foreach (glob($imgs) as $src) {
                            //    $img = $src;
                            //    imagem::reize($img, str_replace('det','tmb',$img), '80', '95');
                            //}
                            
                            $this->obj->update($data, $this->_pk.' = ' . $id);
                            $this->db->commit();
                            $this->_helper->redirector('index');
                        }
                        else{
                            echo '<script>alert("'.$msg.'");</script>';
                            $this->view->data = $formData;
                        }

                    }
                    
                    
                } else {
                
                    if($cpf === TRUE){
                        $msg = 'CPF já utilizado';
                        echo '<script>alert("'.$msg.'");</script>';
                    }
                    
                    //$this->form->populate($formData);
                    $this->view->data = $formData;
                }
            } catch(Exception $ex) {
                Zend_Loader::loadClass('errordb','library');
                echo '<script>alert("'.$ex->getMessage().'");</script>';
                // echo $ex->getMessage();
                //$this->form->populate($formData);
                $this->view->data = $formData;
                $this->db->rollBack();
            }
        } else {
            $id = $this->_getParam('id', 0);
            if ($id > 0) {
                $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
                $data = $this->db->fetchRow($query);
                $data['ALU_DATNAS'] = Datetools::amd_dma($data['ALU_DATNAS']);
                $data['ALU_PAIDATNAS'] = Datetools::amd_dma($data['ALU_PAIDATNAS']);
                $data['ALU_MAEDATNAS'] = Datetools::amd_dma($data['ALU_MAEDATNAS']);
                //$this->form->populate($data);
                
                $obj = new Admin_Model_DbTable_Usuarios();
                $user = $obj->fetchRow('USU_ID = ' . $data['USU_ID']);
                $data['ALU_LOG'] = $user['USU_LOGIN'];
                
                $this->view->data = $data;
            }
        }
    }

    public function deleteAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $id = $this->_getParam('id', 0);
        if ((int)$id > 0 ) {   
            $data = array($this->_sta_col => 0);
            //$ok = (bool) $this->obj->update($data, $this->_pk . ' = ' . $id);
            
            $id = $this->_getParam('id', 0);
            $src = $this->_getParam('src', '');

            $obj = new Admin_Model_DbTable_Galeria();
            $ok = (bool) $obj->delete($this->_pk . ' = ' . $id);
            
            $ok = (bool) $this->obj->delete($this->_pk . ' = ' . $id);
            
            foreach (glob($this->dir.$id.'_*.*') as $file) {
                unlink($file);
            }
            
            Zend_Loader::loadClass('imagem','library');
            imagem::delete($id,$this->dir,TRUE);
            
            $query = 'SELECT SQL_CACHE USU_ID FROM '.$this->_table.' WHERE '.$this->_pk .' = ' . $id;
            $data = $this->db->fetchRow($query);

            $user = array('USU_STA' => 0);
            $ok = (bool) $this->db->update('USUARIOS',$user,'USU_ID = '.$data['USU_ID']);
        }       
        //echo json_encode(array('ok' => $ok));
        $this->_helper->redirector('index');
    }
    
    public function findAction(){
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
       
        $q = strtolower($this->_getParam('term'));
        
        $index = array();
        $result = array();
        $where = 'ALU_NOM like "'.$q.'%" and ALU_STA = 1';
        $rows = $this->obj->fetchAll();
        foreach ($rows as $res) {
            if(strpos(strtolower($res->ALU_NOM), $q) !== false && array_search($res->ALU_NOM, $index) === false) {
                array_push($result, array("id"=>$res->ALU_ID,"label"=>$res->ALU_NOM,"value" => $res->ALU_NOM));
                $index[] = $res->ALU_NOM; 
            }
            if(count($result) > 20) break;
        }

        echo json_encode($result);
    }
    
    public function popupAction(){
        $this->_helper->layout->disableLayout(); 
        //$this->_helper->layout->setLayout('layout_clean');
        
        $where = 'ALU_ID > 0';
        
        $dscfiltro = $this->_getParam('dscfiltro', '');
        if($dscfiltro > '') $where .= ' and ALU_NOM like "%'. $dscfiltro.'%"';
        $this->view->dscfiltro = $dscfiltro;
        
        $ALU_STA = $this->_getParam('ALU_STA ', 0);
        if($ALU_STA > 0) $where .= ' and ALU_STA  = '.$ALU_STA;
        
        $result = $this->obj->fetchAll($this->obj->select()->where($where)->order(array('ALU_NOM ASC')));
        $this->view->result = $this->paginacao($result,10);
        
    }
    
    public function verificanomeAction(){
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        $ok = false;
        $id = 0;
        $msg = '';
        
        $nome = $this->_getParam('nome', '');
        if($nome > ''){
            $where = 'ALU_NOM like "'.$nome.'"';
            $result = $this->obj->fetchRow($where);
            if($result->ALU_ID > 0 && isset($result->ALU_ID)){
                $ok = true;
                $id = $result->ALU_ID;
            }
            else $msg = 'Não existe cadastro de aluno com esse nome!';
        } else $ok = true;
        
        echo json_encode(array('ok' => $ok, 'id' => $id, 'msg' => $msg));
    }

    public function fotoAction(){
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('dominio','library');
        Zend_Loader::loadClass('imagem','library');
        
        if($this->getRequest()->isPost()) { 
            $formData = $this->getRequest()->getPost();
   
            $id = (int) $formData[$this->_pk];//$this->form->getValue($this->_pk);

            $returnImg = imagem::upload($this->_file_col,$this->dir,$id.'_det');

            imagem::rename($id.'_det',$this->dir,$returnImg['filenew']);

            $imgs = $this->dir.$id.'_det.*'; 
            foreach (glob($imgs) as $src) {
                $img = $src;
                imagem::reize($img, str_replace('det','tmb',$img), '80', '95');
                imagem::reize($img, str_replace('det','crt',$img), '80', '95');
                imagem::reize($img, str_replace('det','ead',$img), '60', '50');
            }
           
           
        }else{
        
            $where = ' ALU_ID = '.$this->_getParam('id', '0');
            $result = $this->obj->fetchRow($where);
            $this->view->data = $result;
        }
    }
    
    public function salvarfotoAction(){
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        $img = '';
        $id = $this->_getParam('id', '0');
        
        if ($_POST['type'] == "pixel") {
            // input is in format 1,2,3...|1,2,3...|...
            $im = imagecreatetruecolor(240,240);

            foreach (explode("|", $_POST['image']) as $y => $csv) {
                foreach (explode(";", $csv) as $x => $color) {
                    imagesetpixel($im, $x, $y, $color);
                }
            }
        } else {
            // input is in format: data:image/png;base64,...
            $im = imagecreatefrompng($_POST['image']);
            $ok = imagepng($im,$this->dir.$id.'.png');
            @chmod($this->dir.$id.'.png', 0755);
            $img = '<img src="'.$this->_urlbase.'/'.$this->dir.$id.'.png?'.rand(100,10000).'" />';
            
            // cria foto tumb do aluno 
            Zend_Loader::loadClass('WideImage','library/wideimage/lib/');
            
            $image = WideImage::load($this->dir.$id.'.png');
            //$resized = $image->resize('70', '100', 'fill'); 
            $resized = $image->resize('240', '240', 'outside')->crop('50% - '.('240'/2), '50% - '.('240'/2), '240', '240');
            $filenewp = $this->dir.$id.'_det.png';
            $resized->saveToFile($filenewp);
            @chmod($filenewp, 0755);
            
            $image = WideImage::load($this->dir.$id.'.png');
            //$resized = $image->resize('70', '100', 'fill'); 
            $resized = $image->resize('80', '95', 'outside')->crop('50% - '.('80'/2), '50% - '.('95'/2), '80', '95');
            $filenewp = $this->dir.$id.'_tmb.png'; 
            $resized->saveToFile($filenewp); 
            @chmod($filenewp, 0755);
            
            $image = WideImage::load($this->dir.$id.'.png');
            //$resized = $image->resize('70', '100', 'fill'); 
            $resized = $image->resize('80', '95', 'outside')->crop('50% - '.('80'/2), '50% - '.('95'/2), '80', '95');
            $filenewp = $this->dir.$id.'_crt.png'; 
            $resized->saveToFile($filenewp); 
            @chmod($filenewp, 0755);
            
            $image = WideImage::load($this->dir.$id.'.png');
            //$resized = $image->resize('70', '100', 'fill'); 
            $resized = $image->resize('60', '50', 'outside')->crop('50% - '.('80'/2), '50% - '.('95'/2), '80', '95');
            $filenewp = $this->dir.$id.'_ead.png'; 
            $resized->saveToFile($filenewp); 
            @chmod($filenewp, 0755);
        }
        
        echo json_encode(array('ok' => $ok,'img' => $img));
    }
    
    public function viewAction() {
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        Zend_Loader::loadClass('operacoes','library');
         
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_view.js');
        
        $id = $this->_getParam('id', 0);
        
        if((int)$id > 0){
            $row = $this->obj->fetchRow($this->_pk . ' = ' . $id);
            $this->view->row = $row;
        }
        else
            $this->_helper->redirector('index');
 
        $mat = new Admin_Model_DbTable_Matricula();
        $this->view->mat = $mat->fetchRow('ALU_ID = '.$row['ALU_ID']);
       
        $_SESSION['img_id'] = $id;
        $_SESSION['dir_url'] = $_SERVER['DOCUMENT_ROOT'].$this->_urlbase.'/';
        $_SESSION['dir_uploud'] = $_SESSION['dir_url'].$this->dir;
        $_SESSION['img_width'] = 196;
        $_SESSION['img_height'] = 196;
        
    }
    
    public function materialAction(){
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('imagem','library');
        Zend_Loader::loadClass('dominio','library');
        Zend_Loader::loadClass('elemform','library');
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_material.js');
        
        $id = $this->_getParam('id', 0);
        if ($id > 0) {
            $data = $this->obj->fetchRow($this->_pk . ' = ' . $id)->toArray();
            $this->view->data = $data;
            
            $obj = New Admin_Model_DbTable_Matricula();
            $mat = $obj->fetchRow($this->_pk . ' = ' . $id.' and MAT_STA = 1');
            $this->view->mat = $mat;
        }
    }
    
    public function recebimentomaterialAction(){
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender(); 
        
        Zend_Loader::loadClass('datetools','library');
        
        $data = array();
        if($this->getRequest()->isPost()) {
            
            $obj = new Admin_Model_DbTable_MatriculaHasMaterial();
            
            $data['MAT_ID'] = $this->_getParam('mat');
            $data['MTL_ID'] = $this->_getParam('mtl');
            $data['MTL_DAT'] = date('Y-m-d');
            $row = $obj->fetchRow('MAT_ID = '.$data['MAT_ID'].' and MTL_ID = '.$data['MTL_ID']);
            if($row['MTL_ID'] > 0 && isset($row['MTL_ID']))  
                $obj->delete('MAT_ID = '.$data['MAT_ID'].' and MTL_ID = '.$data['MTL_ID']);
            else 
            {
                $obj->insert($data); 
                echo json_encode(array('ok' => TRUE, 'dt' => Datetools::amd_dma($data['MTL_DAT'])));
            }
        }
    }
    
    public function editfotoAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        
        $id = $this->_getParam('id');
        //$src = $this->_getParam('src');
        $op = $this->_getParam('op', 0);
        
        Zend_Loader::loadClass('imagem','library');
        
        foreach(glob($this->dir.$id.'_tb.*') as $file) $filenewp = $file;

        foreach(glob($this->dir.$id.'.*') as $file) $filename = $file;
        
        $tam_img = getimagesize($filenewp);
        $tam = $tam_img[0];
        
        if($op == 0){
            if($tam == '196')
                $ok = imagem::reize($filename,$filenewp, 394, 394);
            else
                $ok = imagem::reize($filename,$filenewp,196,196);
        }else{ 
            $tam = $this->_getParam('tam','');
            
            if($tam == '1')
                $ok = imagem::reize($filename,$filenewp,196,196);  
            else if($tam == '2')
                $ok = imagem::reize($filename,$filenewp, 394, 196);
            else if($tam == '3')
                $ok = imagem::reize($filename,$filenewp, 196, 394);
            else if($tam == '4')
                $ok = imagem::reize($filename,$filenewp, 394, 394);
                
        }
        
   
        echo json_encode(array('ok' => $ok));
    }
    public function delfotoAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        
        $id = $this->_getParam('id', 0);
        $src = $this->_getParam('src', '');
        $alu_id = $this->_getParam('alu_id', 0);
        if ((int)$id > 0 ) {
            
            $src = explode('/', $src);
            
            $obj = new Admin_Model_DbTable_Galeria();
            $ok = $obj->delete('GAL_IDIMG like "'.$src[count($src)-1].'" AND ALU_ID = "'.$alu_id.'"');
            
            if($ok){
                Zend_Loader::loadClass('imagem','library');
                imagem::delete($id,$this->dir,TRUE);
            }
        }
        
        echo json_encode(array('ok' => $ok));
    }
    public function ordemfotoAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        
        $id = $this->_getParam('id');
        $ord = $this->_getParam('ordem');
        
        $data = array(
            //'GAL_IDIMG' => $id,
            //'GAL_NOM' => $this->_getParam('titulo'),
            'GAL_ORD'   => $ord,
        );
           
            
        $obj = new Admin_Model_DbTable_Galeria();
        $ok = $obj->update($data, 'GAL_IDIMG like "' . $id .'"');
        
        echo json_encode(array('ok' => $ok));
    }
    
    public function nomfotoAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        
        $id = $this->_getParam('id');
        $nome = $this->_getParam('nome');
        
        $data = array(
            //'GAL_IDIMG' => $id,
            'GAL_NOM' => $nome,
        );        
            
        $obj = new Admin_Model_DbTable_Galeria();
        $ok = $obj->update($data, 'GAL_IDIMG like "' . $id .'"');
        
        echo json_encode(array('ok' => $ok));
    }
    
    public function cpfdplAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        $msg = '';
        
        $id = $this->_getParam('id', 0);
        $cpf = $this->_getParam('cpf', '');
        $op = $this->_getParam('op', '');
        
        $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE ALU_ID <> '.$id.' and ALU_CPF = "'.$cpf.'"';
        $result = $this->db->fetchAll($query);
                
        foreach($result as $res){
            if($res['ALU_CPF'] == $cpf || $res['ALU_PAICPF'] == $cpf || $res['ALU_MAECPF'] == $cpf){
                $ok = TRUE;
                $msg = 'CPF já utilizado';
            }
        }
        
        
        echo json_encode(array('ok' => $ok, 'msg' => $msg));
    }
    
    public function verificarCPF($cpf,$id = 0){
        
        $where = ' ALU_CPF = "'.$cpf.'"';
        if($id > 0) $where .= ' AND '.$this->_pk . ' <> ' . $id;
        
        $query = 'SELECT SQL_CACHE ALU_ID, ALU_CPF, ALU_PAICPF, ALU_MAECPF FROM '.$this->_table.' WHERE '.$where;
        $data = $this->db->fetchRow($query);
        
        if($data['ALU_ID'] >0) return TRUE;
        else return FALSE;
    }
    
    public function galeriaAction() {
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('imagem','library');
        Zend_Loader::loadClass('dominio','library');
        Zend_Loader::loadClass('elemform','library');
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_view.js');
        
        $id = $this->_getParam('id', 0);
        
        $_SESSION['img_id'] = $id;
        $_SESSION['dir_url'] = $_SERVER['DOCUMENT_ROOT'].$this->_urlbase.'/';
        $_SESSION['dir_uploud'] = $_SESSION['dir_url'].$this->dir;
        $_SESSION['img_width'] = 196;
        $_SESSION['img_height'] = 196;
        $_SESSION['insert'] = 'aluno';
        
        if ($id > 0) {
            $data = $this->obj->fetchRow($this->_pk . ' = ' . $id)->toArray();
            $data['ALU_DATNAS'] = Datetools::amd_dma($data['ALU_DATNAS']);
            $data['ALU_PAIDATNAS'] = Datetools::amd_dma($data['ALU_PAIDATNAS']);
            $data['ALU_MAEDATNAS'] = Datetools::amd_dma($data['ALU_MAEDATNAS']);
            //$this->form->populate($data);
            $this->view->data = $data;
            
            $this->view->dir = $this->dir;
            
            $obj = new Admin_Model_DbTable_Galeria();
            $result = $obj->fetchAll($obj->select()->where($this->_pk . ' = ' . $id)->order('GAL_ORD ASC'));
            $this->view->result = $this->paginacao($result,9);
            
        }
    }
    public function matriculaAction(){
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');

        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_view.js');
        $id = $this->_getParam('id', 0);
        if ($id > 0) {

            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk .' = '.$id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
            $query = 'SELECT SQL_CACHE * FROM MATRICULA WHERE '.$this->_pk .' = '.$id. ' ORDER BY MAT_DAT DESC';
            $mat = $this->db->fetchAll($query);
            $this->view->result = $mat;
        }
    }
    
    public function senhaAction(){
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_senha.js?1=3');
        
        $id = $this->_getParam('id', 0);
        
        if ($id > 0) {
            
            $data = $this->obj->fetchRow($this->_pk . ' = ' . $id)->toArray();
            
            $query = 'SELECT SQL_CACHE USU_LOGIN FROM USUARIOS WHERE USU_ID = '.$data['USU_ID'];
            $usu = $this->db->fetchRow($query);
            $data['ALU_LOG']= $usu['USU_LOGIN'];
            
            if($this->getRequest()->isPost()) {
                $formData = $this->getRequest()->getPost();
                
                $formData['ALU_LOG'] = tools::retiraAcentos($formData['ALU_LOG']);
                $formData['ALU_LOG'] = strtolower($formData['ALU_LOG']);
                
                // atualizar login no form, no post
                $data['ALU_LOG']= $formData['ALU_LOG'];
                
                $id = (int) $formData[$this->_pk];
                $return = FALSE;
                
                if(strlen($formData['ALU_SNH']) > 1){
                    $user['USU_LOGIN'] = $formData['ALU_LOG'];
                    $user['USU_SENHA'] = sha1($formData['ALU_SNH']);
                    
                    $obj = new Admin_Model_DbTable_Usuarios();
                    $return = $obj->update($user, 'USU_ID = ' . $data['USU_ID']);
                }
                else{
                    echo '<script>alert("Erro: Senha não informada!");</script>';
                }
                
                if($return) echo '<script>alert("Senha alterada com sucesso!");</script>';
                
            }
            
            $this->view->data = $data;
            
        }
    }
    
    public function opcaovestibularAction(){
    
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_opcaovestibular.js?1=6');
        
        $id = $this->_getParam('id', 0);
        
        if ($id > 0) {
            
            $data = $this->obj->fetchRow($this->_pk . ' = ' . $id)->toArray();
            $this->view->data = $data;
           
            if($this->getRequest()->isPost()) {
                $formData = $this->getRequest()->getPost();

                $id = (int) $formData[$this->_pk];
                $return = FALSE;
                
                $obj = new Admin_Model_DbTable_AlunoHasOpcaovestibular();
                $obj->delete('ALU_ID = '.(int) $id);
                if(isset($formData['FAC_ID']))
                foreach($formData['FAC_ID'] as $key => $fac) {
                    if($fac > 0 || $formData['CUF_ID'][$key] > 0){
                        $data = array(
                            'ALU_ID'=> $id, 
                            'FAC_ID'=> $fac,
                            'CUF_ID'=> $formData['CUF_ID'][$key]
                        );
                        $obj->insert($data); 
                    }
                    
                    $return = TRUE;
                }
                              
                if($return) echo '<script>alert("Opções de Vestibular salvo com sucesso!");</script>';   
            }
        
        }
        
    }
    
    public function validaremailAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        $msg = '';
        
        $id = $this->_getParam('id', 0);
        $email = trim($this->_getParam('email', ''));
        
        if($email > ''){ 
            //$where = 'ALU_ID <> '.$id.' and ALU_EML like "'.$email.'"';
            //$result = $this->obj->fetchAll($this->obj->select()->where($where)->order($this->_order_col.' '.$this->_order));
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE ALU_ID <> '.$id.' and ALU_EML like "'.$email.'"';
            $result = $this->db->fetchAll($query);
            
            foreach($result as $res){
                if($res['ALU_EML'] == $email || $res['ALU_PAIEML'] == $email || $res['ALU_MAEEML'] == $email){
                    $ok = TRUE;
                    $msg = 'E-mail já cadastrado';
                }
            }
        }
        
        echo json_encode(array('ok' => $ok, 'msg' => $msg));
    }
    
    public function validarloginAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        
        $ok = FALSE;
        $msg = '';
        
        $id = $this->_getParam('id', 0);
        $login = trim($this->_getParam('login', ''));
        
        $where = '';
        if($id > 0){
            $where = 'ALU_ID = '.$id.' ';
            $row = $this->obj->fetchRow($where);
            $where = '';
            if($row->USU_ID > 0) $where = 'USU_ID <> '.$row->USU_ID.' and ';
        }
        
        $obj = new Admin_Model_DbTable_Usuarios();
        
        $where .= 'USU_LOGIN like "'.$login.'"';
        $result = $obj->fetchAll($where);
            
        foreach($result as $res){
            if($res->USU_LOGIN == $login){
                $ok = TRUE;
                $msg = 'Login já cadastrado';
            }
        }
 
        echo json_encode(array('ok' => $ok, 'msg' => $msg));
    }
    
    
    public function novaopcaovestibularAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        $html .= '<div class="row">
                <a href="javascript:void(0)" onclick="$(this).parent().remove();" style="float: right">excluir</a>
                <div class="ln"><strong style="display: inline-block;width: 135px;">Instituição de Ensino</strong>
        ';
        
        $html .= '<select name="FAC_ID[]" class="chosen-select"><option value="0">Nenhum</option>';
        $obj = new Admin_Model_DbTable_Faculdade();
        $row = $obj->fetchAll($obj->select()->order('FAC_NOM ASC'));
        foreach($row as $r){         
            $html .= '<option '.$selected.' value="'.$r['FAC_ID'].'">'.$r['FAC_NOM'].'</option>'; 
        }
        $html .= '</select>';
        
        $html .= '</div>
                <div class="ln"><strong style="display: inline-block;width: 135px;">Cursos de Graduação</strong>
                ';

        $html .= '<select name="CUF_ID[]" class="chosen-select"><option value="0">Nenhum</option>';
        $obj = new Admin_Model_DbTable_Cursofaculdade();
        $row = $obj->fetchAll($obj->select()->order('CUF_NOM ASC'));
        foreach($row as $r){           
            $html .= '<option '.$selected.' value="'.$r['CUF_ID'].'">'.$r['CUF_NOM'].'</option>'; 
        }
        $html .= '</select>';
        $html .= '
                </div>
                
            </div><script>$(function(){ $(".chosen-select").chosen(); });</script>
        ';
        
        echo json_encode(array('ok' => TRUE, 'html' => $html));
        
    }
    
    public function aprovacaoAction() {
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_aprovacao.js?1=6');
        
        
        $id = $this->_getParam('id', 0);
        $apv_id = $this->_getParam('apv_id', 0);
        $op = $this->_getParam('op', '');
        
        if($id > 0) {
            
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
        }else{ $this->_helper->redirector('index'); } 
        
        
        if($op == 'edit'){
            $this->_helper->viewRenderer->setRender('aprovacao-edit');
            
            if($this->getRequest()->isPost()) {
                $formData = $this->getRequest()->getPost();
                $data = $formData;
                
                unset($data['submit']);
                $data['FAC_ID'] = isset($data['FAC_ID'][0]) ? $data['FAC_ID'][0]:0;
                $data['CUF_ID'] = isset($data['CUF_ID'][0]) ? $data['CUF_ID'][0]:0;
                $data['APV_DAT'] = date('Y-m-d');
                
                if($formData['APV_ID'] == 0)
                    $this->db->insert('APROVACAO',$data);
                else
                    $this->db->update('APROVACAO',$data,'APV_ID = '.$formData['APV_ID']);
                
                $urlOptions = array('controller'=>$this->_controller,'action'=>'aprovacao','id'=>$id,'op'=>'');
                $this->_helper->redirector->gotoRoute($urlOptions);
            }
            else if($apv_id > 0){
                $query = 'SELECT SQL_CACHE * FROM APROVACAO WHERE APV_ID = ' . $apv_id;
                $apv = $this->db->fetchRow($query);
                $this->view->apv = $apv;
            }
        } else if($op == 'delete'){
            
            $ok = (bool) $this->db->delete('APROVACAO', 'APV_ID = ' . $apv_id );
        
            $urlOptions = array('controller'=>$this->_controller,'action'=>'aprovacao','id'=>$id,'op'=>'');
            $this->_helper->redirector->gotoRoute($urlOptions);
            
        }else{
            $query = 'SELECT SQL_CACHE a.*,f.FAC_NOM, c.CUF_NOM '
                    . ' FROM APROVACAO a, FACULDADE f, CURSOFACULDADE c'
                    . ' WHERE a.FAC_ID = f.FAC_ID AND a.CUF_ID = c.CUF_ID '
                    . ' AND '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;
        }   
        
    }
    
    public function boletimdesempenhoAction() {
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskMoney.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskedinput.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/mask.js');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_boletimdesempenho.js');
        
        
        $id = $this->_getParam('id', 0);
        $btd_id = $this->_getParam('btd_id', 0);
        $op = $this->_getParam('op', '');
        
        if($id > 0) {
            
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
        }else{ $this->_helper->redirector('index'); } 
        
        
        if($op == 'edit'){
            $this->_helper->viewRenderer->setRender('boletimdesempenho-edit');
            
            if($this->getRequest()->isPost()) {
                $formData = $this->getRequest()->getPost();
                
                $btd_id = $formData['BTD_ID'];
                        
                $data = $formData;
                
                unset($data['submit']);
                unset($data['d']);
                unset($data['n']);
                unset($data['p']);
                unset($data['a']);
                
                //echo '<pre>';print_r($data);exit();
                
                
                if($formData['BTD_ID'] == 0){ 
                    $data['BTD_STA'] = 1;
                    $this->db->insert('BOLETIM_DESEMPENHO',$data);
                    $btd_id = $this->db->lastInsertId();
                }
                else
                    $this->db->update('BOLETIM_DESEMPENHO',$data,'BTD_ID = '.$btd_id);
                
                
                $this->db->delete('BOLETIM_DESEMPENHO_DISC','BTD_ID = ' . $btd_id);
                if(isset($formData['d']))
                foreach($formData['d'] as $key => $d){

                    if($d > 0){

                        $data2 = array(
                            'BTD_ID'    => $btd_id,
                            'DCP_ID'    => $d,
                            'BDD_NQU'   => $formData['n'][$key],
                            'BDD_PES'   => $formData['p'][$key],
                            'BDD_ACT'   => $formData['a'][$key],
                        );

                        $this->db->insert('BOLETIM_DESEMPENHO_DISC',$data2);

                    }
                }
                
                
                $urlOptions = array('controller'=>$this->_controller,'action'=>'boletimdesempenho','id'=>$id,'op'=>'');
                $this->_helper->redirector->gotoRoute($urlOptions);
            }
            else if($btd_id > 0){
                $query = 'SELECT SQL_CACHE * FROM BOLETIM_DESEMPENHO WHERE BTD_ID = ' . $btd_id;
                $data = $this->db->fetchRow($query);
                $this->view->result = $data;
            }
        } else if($op == 'delete'){
            
            $this->db->beginTransaction();
            try {
  
                $ok = (bool) $this->db->delete('BOLETIM_DESEMPENHO_DISC', 'BTD_ID = ' . $btd_id );

                $ok = (bool) $this->db->delete('BOLETIM_DESEMPENHO','ALU_ID = '.$id.' AND BTD_ID = ' . $btd_id );

                 $this->db->commit();
                
            } catch (Exception $ex) {
                $this->db->rollBack();
            }
            
            $urlOptions = array('controller'=>$this->_controller,'action'=>'boletimdesempenho','id'=>$id,'op'=>'');
            $this->_helper->redirector->gotoRoute($urlOptions);
            
        }else{
            $query = ' SELECT SQL_CACHE b.*, f.FAC_NOM, p.FAP_NOM'
                    .' FROM BOLETIM_DESEMPENHO b, FACULDADE f, FACULDADE_PROVA p '
                    .' WHERE '.$this->_pk . ' = ' . $id.' '
                    .' AND b.FAC_ID = f.FAC_ID '
                    .' AND b.FAC_ID = p.FAC_ID AND b.FAP_ID = p.FAP_ID ';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;
        }   
        
    }
    
    public function buscaprovasAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        $id = $this->_getParam('id', 0);
        $html = '';
        
        $html .= '<option value="0">Nenhum</option>';
        $query = 'SELECT SQL_CACHE FAP_ID,FAP_NOM FROM FACULDADE_PROVA WHERE FAC_ID = '.$id.' AND FAP_STA = 1 ORDER BY FAP_NOM ASC';
        $row = $this->db->fetchAll($query);
        foreach($row as $r){         
            $html .= '<option '.$selected.' value="'.$r['FAP_ID'].'">'.$r['FAP_NOM'].'</option>'; 
        }
        
        echo json_encode(array('ok' => TRUE, 'html' => $html));
        
    }
    
    public function buscadisciplinasAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        $id = $this->_getParam('id', 0);
        $html = '';
        
        if($id > 0){
            $query = 'SELECT SQL_CACHE f.*,d.DCP_NOM FROM FACULDADE_PROVA_DISC f, DISCIPLINA d WHERE f.DCP_ID = d.DCP_ID AND f.FAP_ID = '.$id;
            $result = $this->db->fetchAll($query);
            foreach($result as $row){
                
                $html .= '<div class="cnt_el">';
                $html .= 'Disciplina: <span class="cmp_m">'.$row['DCP_NOM'].'</span> <input type="hidden" name="d[]" value="'.$row['DCP_ID'].'" >';
                $html .= ' &nbsp; Nº Questões: <input type="text" class="nummin" name="n[]" value="'.$row['FPD_NQU'].'" readonly="">';
                $html .= ' &nbsp; Peso: <input type="text" class="nummin" name="p[]" value="'.$row['FPD_PES'].'" readonly="">';
                $html .= ' &nbsp; Acertos: <input type="text" class="nummin" name="a[]" value="" maxlength="3">';
                $html .= '</div>';
            }
        }
        
        $script = '<script type="text/javascript" src="/public/media/js/mask.js"></script>';
        
        echo json_encode(array('ok' => TRUE, 'html' => $html, 'script' => $script ));
        
    }
    
    public function instituicaoensinoanteriorAction(){
            
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskMoney.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskedinput.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/mask.js');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_boletimdesempenho.js');
        
        $id = $this->_getParam('id', 0);
        $iea_id = $this->_getParam('iea_id', 0);
        $op = $this->_getParam('op', '');
        
        if($id > 0) {
            
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
        }else{ $this->_helper->redirector('index'); } 
        
        
        if($op == 'edit'){
            $this->_helper->viewRenderer->setRender('instituicaoensinoanterior-edit');
            
            if($this->getRequest()->isPost()) {
                $formData = $this->getRequest()->getPost();
                
                $iea_id = $formData['IEA_ID'];
                        
                $data = $formData;
                
                unset($data['submit']);
                
                //echo '<pre>';print_r($data);exit();
                           
                if($formData['IEA_ID'] == 0){ 
                    $data['IEA_STA'] = 1;
                    $this->db->insert('INSTITUICAO_ENSINO_ANTERIOR',$data);
                    $iea_id = $this->db->lastInsertId();
                }
                else
                    $this->db->update('INSTITUICAO_ENSINO_ANTERIOR',$data,'IEA_ID = '.$iea_id);
                
                $urlOptions = array('controller'=>$this->_controller,'action'=>'instituicaoensinoanterior','id'=>$id,'op'=>'');
                $this->_helper->redirector->gotoRoute($urlOptions);
            }
            else if($iea_id > 0){
                $query = 'SELECT SQL_CACHE * FROM INSTITUICAO_ENSINO_ANTERIOR WHERE IEA_ID = ' . $iea_id;
                $data = $this->db->fetchRow($query);
                $this->view->result = $data;
            }
        } else if($op == 'delete'){
            
            $this->db->beginTransaction();
            try {
                $ok = (bool) $this->db->delete('INSTITUICAO_ENSINO_ANTERIOR','ALU_ID = '.$id.' AND IEA_ID = ' . $iea_id );

                $this->db->commit();
                
            } catch (Exception $ex) {
                $this->db->rollBack();
            }
            
            $urlOptions = array('controller'=>$this->_controller,'action'=>'instituicaoensinoanterior','id'=>$id,'op'=>'');
            $this->_helper->redirector->gotoRoute($urlOptions);
            
        }else{
            $query = ' SELECT SQL_CACHE * '
                    .' FROM INSTITUICAO_ENSINO_ANTERIOR'
                    .' WHERE '.$this->_pk . ' = ' . $id.' '
                    .' ORDER BY IEA_ANO DESC';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;
        }   
      
    }
    
    public function gerarlogin($nome){
        $login = 'sem_login_'. rand(999, 9999);
        if(strlen($nome) > 0){
            
            Zend_Loader::loadClass('tools','library');
            
            $nome = tools::retiraAcentos($nome);
            $nome = strtolower($nome);
            $nome = str_replace(array('  ','.'),array(' ',' '), $nome); 
            $temp = explode(' ', $nome);
            
            $segundo = $temp[(count($temp) - 1)];
            
            if(strlen($segundo) <= 3      && strlen($temp[(count($temp) - 1)]) >= 3) $segundo = $temp[(count($temp) - 1)];
            else if(strlen($segundo) <= 3 && strlen($temp[(count($temp) - 2)]) >= 3) $segundo = $temp[(count($temp) - 2)];
            else if(strlen($segundo) <= 3 && strlen($temp[(count($temp) - 3)]) >= 3) $segundo = $temp[(count($temp) - 3)];
            else if(strlen($segundo) <= 3 && strlen($temp[(count($temp) - 4)]) >= 3) $segundo = $temp[(count($temp) - 4)];
            
            $login = $temp[0].'.'.$segundo;
            
            $login = $this->verNovoLogin($login,TRUE);
                
        }
        
        return $login;
    }
    
    function verNovoLogin($login,$bool=TRUE){
        if($bool == FALSE){
            return $login;
        }else{
            $bool = FALSE;
            $query = 'SELECT SQL_CACHE USU_LOGIN FROM USUARIOS WHERE USU_LOGIN like "'.$login.'"';
            $result = $this->db->fetchAll($query);
            foreach($result as $res){
                $login = $login.rand(1, 99);
                $bool = TRUE;
            }
            return $this->verNovoLogin($login,$bool);
        }
    }
    
    public function historiconapAction(){
    
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/validation/dist/jquery.validate.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_opcaovestibular.js?1=6');
        
        $id = $this->_getParam('id', 0);
        
        if($id > 0) {
            
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
        }else{ $this->_helper->redirector('index'); } 
        
        
        $where = ' WHERE ALU_ID = '.$id.' AND a.TAH_ID = t.TAH_ID ';
        
        $where .= ' AND a.USU_ID = u.USU_ID ';
        
        $query = ' SELECT SQL_CACHE a.*, t.TAH_NOM, u.USU_NOME '
                .' FROM ATENDIMENTO_NAP a , TIPO_ATENDIMENTONAP t, USUARIOS u '
                .' '.$where.' ORDER BY ATP_DAT DESC, ATP_ID DESC';
        $data = $this->db->fetchAll($query);
        $this->view->result = $data;
        
    }
    
    // boletimdesempenholista
    public function boletimdesempenholistaAction(){
            
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $id = $this->_getParam('id', 0);
        $pul_id = $this->_getParam('pul_id', 0);
        $op = $this->_getParam('op', '');
        
        if($id > 0) {
            
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
        }else{ $this->_helper->redirector('index'); } 
        
        $this->view->op = $op; 
        
        if($pul_id > 0){
            $query = 'SELECT SQL_CACHE * FROM PUBLICACAO_LISTA WHERE PUL_ID = '.$pul_id;
            $lista = $this->db->fetchRow($query);
            $this->view->lista = $lista;
        }
        
        if($op == 'list'){
            
            $this->_helper->viewRenderer->setRender('boletimdesempenholista-edit');
            
            $query = 'SELECT SQL_CACHE p.*, g.PLG_ALT,d.DCP_NOM  ' 
                    .' FROM PUBLICACAO_LISTA_RESPOSTA p, PUBLICACAO_LISTA_GABARITO g, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND g.DCP_ID = d.DCP_ID '
                    .' AND p.PLG_ID = g.PLG_ID '
                    .' AND p.PUL_ID = '.$pul_id
                    .' AND p.ALU_ID = '.$id
                    .' ORDER BY p.PLR_QST';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data; 
            
        }else if($op == 'edit'){  
            
            $this->_helper->viewRenderer->setRender('boletimdesempenholista-edit');
            
            $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM  ' 
                    .' FROM PUBLST_QSTDISALU p, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND p.PUL_ID = '.$pul_id
                    .' AND ALU_ID = '.$id
                    .' ORDER BY d.DCP_NOM, p.PQD_ACR DESC, p.DCP_ID, p.PUL_ID';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;    
            
        }else{
            
            $query = 'SELECT SQL_CACHE p.*, a.ALU_ID FROM  PUBLICACAO_LISTA p, PUBLICACAO_LISTA_ALUNO a '
                    .' WHERE p.PUL_ID = a.PUL_ID AND a.ALU_ID = ' . $id
                    .' ORDER BY PUL_DATENC DESC, PUL_HORENC DESC';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;
        }   
      
    }
    
    // boletimdesempenholista
    public function boletimdesempenhosimuladoAction(){
        
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskMoney.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/jquery.maskedinput.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/mask.js?1=3');
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $id = $this->_getParam('id', 0);
        $pls_id = $this->_getParam('pls_id', 0);
        $op = $this->_getParam('op', '');
        
        $this->view->pls_id = $pls_id;
        if($id > 0) {
            
            $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id;
            $data = $this->db->fetchRow($query);
            $this->view->data = $data;
            
        }else{ $this->_helper->redirector('index'); } 
        
        $this->view->op = $op; 
        
        
        if($pls_id > 0){
            $query = 'SELECT SQL_CACHE * FROM PUBLICACAO_SIMULADO WHERE PLS_ID = '.$pls_id;
            $lista = $this->db->fetchRow($query);
            $this->view->lista = $lista;
        }
        
        if($lista['PLS_STA'] < 2){
            $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_aluno_boletimdesempenhosimulado.js?1=9');
        }
        
        if($op == 'list'){
            
            $this->_helper->viewRenderer->setRender('boletimdesempenhosimulado-edit');
            
            $query = 'SELECT SQL_CACHE p.*, g.PSG_ALT,d.DCP_NOM  ' 
                    .' FROM PUBLICACAO_SIMULADO_RESPOSTA p, PUBLICACAO_SIMULADO_GABARITO g, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND g.DCP_ID = d.DCP_ID '
                    .' AND p.PSG_ID = g.PSG_ID '
                    .' AND p.PLS_ID = '.$pls_id
                    .' AND p.ALU_ID = '.$id
                    .' ORDER BY p.PSR_QST';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data; 
            
        }else if($op == 'edit'){  
            
            $this->_helper->viewRenderer->setRender('boletimdesempenhosimulado-edit');
            
            $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM  ' 
                    .' FROM PUBLST_QSTDISALU p, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND p.PUL_ID = '.$pul_id
                    .' AND ALU_ID = '.$id
                    .' ORDER BY d.DCP_NOM, p.PQD_ACR DESC, p.DCP_ID, p.PUL_ID';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;    
            
        }else{
            
            $query = 'SELECT SQL_CACHE p.*, a.ALU_ID FROM  PUBLICACAO_SIMULADO p, PUBLICACAO_SIMULADO_ALUNO a '
                    .' WHERE p.PLS_ID = a.PLS_ID AND a.ALU_ID = ' . $id
                    .' ORDER BY PLS_DATENC DESC, PLS_HORENC DESC';
            $data = $this->db->fetchAll($query);
            $this->view->result = $data;
        }   
      
    }
    public function alternativaAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $msg = '';
        $ALU_ID = $this->_getParam('ALU_ID', 0);
        $PLS_ID = $this->_getParam('PLS_ID', 0);
        $PSG_ID = $this->_getParam('PSG_ID', 0);
        $DCP_ID = $this->_getParam('DCP_ID', 0);
        $num_qst = $this->_getParam('num_qst', 0);
        $qst_alt = $this->_getParam('qst_alt', '0');
        $stalngetg = $this->_getParam('stalngetg', '0');
        
        //$ok = $this->validaencerramento($PLS_ID);
        
        if($ok == TRUE){ 
            $msg = 'Lista de teste finalizada!';
            echo json_encode(array('ok' => FALSE,'msg' => $msg));
            return '';
        }

        $where = ' PSG_ID = '.$PSG_ID.' AND PLS_ID = '.$PLS_ID.' AND ALU_ID = '.$ALU_ID. ' AND PSR_QST = '.$num_qst.' AND DCP_ID ='.$DCP_ID;
        $query = 'SELECT SQL_CACHE PSR_ID FROM PUBLICACAO_SIMULADO_RESPOSTA WHERE '.$where;
        $row = $this->db->fetchRow($query);
        $this->db->beginTransaction();
        try{
            
            $this->db->update('PUBLICACAO_SIMULADO_ALUNO',array('PSA_STA'=>'1'),'PLS_ID = '.$PLS_ID.' AND ALU_ID = '.$ALU_ID);
            
            if($row['PSR_ID'] > 0){
                $data = array(
                    'PSR_ALT' => $qst_alt,
                );
                $ok = (bool) $this->db->update('PUBLICACAO_SIMULADO_RESPOSTA',$data,'PSR_ID = '.$row['PSR_ID']);
                $ok = true;
            }else{
                $data = array(
                    'PSG_ID'    => $PSG_ID,
                    'PLS_ID'    => $PLS_ID,
                    'ALU_ID'    => $ALU_ID,
                    'PSR_QST'   => $num_qst,
                    'PSR_ALT'   => $qst_alt,
                    'DCP_ID'    => $DCP_ID,
                    'PSR_STALNGETG' => $stalngetg
                );
                //echo '<pre>';print_r($data);
                $ok = (bool) $this->db->insert('PUBLICACAO_SIMULADO_RESPOSTA',$data);
            }
            
            $this->db->commit();
            
        } catch(Exception $ex) {
            $msg = $ex->getMessage();
            $ok = FALSE;
            $this->db->rollBack();
        } 
        echo json_encode(array('ok' => $ok,'msg' => $msg));
    }
    
    public function confirmaralterarAction() { 
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $msg = '';
        $ALU_ID = $this->_getParam('ALU_ID', 0);
        $PLS_ID = $this->_getParam('PLS_ID', 0);
        $PSG_ID = $this->_getParam('PSG_ID', 0);
        $DCP_ID = $this->_getParam('DCP_ID', 0);
        $num_qst = $this->_getParam('num_qst', 0);
        $qst_alt = $this->_getParam('qst_alt', '0');
        $op = $this->_getParam('op', '0');
        
        $where = ' PSG_ID = '.$PSG_ID.' AND PLS_ID = '.$PLS_ID.' AND ALU_ID = '.$ALU_ID. ' AND PSR_QST = '.$num_qst;
        $query = 'SELECT SQL_CACHE PSR_ID FROM PUBLICACAO_SIMULADO_RESPOSTA WHERE '.$where;
        $row = $this->db->fetchRow($query);
        
        $this->db->beginTransaction();
        try{
            if($row['PSR_ID'] > 0){
                $data = array(
                    'PSR_STA' => $op,
                );
                $ok = (bool) $this->db->update('PUBLICACAO_SIMULADO_RESPOSTA',$data,'PSR_ID = '.$row['PSR_ID']);
            }
            
            $this->db->commit();
            
        } catch(Exception $ex) {
            $msg = $ex->getMessage();
            $ok = FALSE;
            $this->db->rollBack();
        } 
        echo json_encode(array('ok' => $ok,'msg' => $msg));
    }
    
    public function validaencerramento($PLS_ID){
        $ok = FALSE;
        $where = ' PLS_ID = '.$PLS_ID;
        $query = 'SELECT SQL_CACHE PLS_ID, PLS_DATENC, PLS_HORENC, PLS_STA FROM PUBLICACAO_SIMULADO WHERE '.$where;
        $row = $this->db->fetchRow($query);
        
        if($row['PLS_STA'] == 2){
            $ok = TRUE;
        }
        else{
            $ok = FALSE;
        }
        
        return $ok;
    }
    
    public function verificaencerramentoAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $msg = '';
        $ALU_ID = $this->_getParam('ALU_ID', 0);
        $PLS_ID = $this->_getParam('PLS_ID', 0);
        
        $ok = $this->validaencerramento($PLS_ID);
        
        if($ok == TRUE) $msg = 'Lista de teste finalizada!';
        
        
        echo json_encode(array('ok' => $ok,'msg' => $msg));
    }
    
    public function oplngetgAction() { 
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $msg = '';
        $ALU_ID = $this->_getParam('ALU_ID', 0);
        $PLS_ID = $this->_getParam('PLS_ID', 0);
        $DCP_ID = $this->_getParam('DCP_ID', 0);

        if($ALU_ID > 0 && $PLS_ID > 0 && $DCP_ID > 0){
            $this->db->beginTransaction();
            try{
                $data = array(
                    'DCP_ID' => $DCP_ID,
                );
                $ok = (bool) $this->db->update('PUBLICACAO_SIMULADO_ALUNO',$data,'PLS_ID = '.$PLS_ID.' AND ALU_ID = '.$ALU_ID);
                
                $ok = (bool) $this->db->delete('PUBLICACAO_SIMULADO_RESPOSTA','PLS_ID = '.$PLS_ID.' AND ALU_ID = '.$ALU_ID.' AND PSR_STALNGETG = 1');
                
                $ok = true;
                
                $this->db->commit();

            } catch(Exception $ex) {
                $msg = $ex->getMessage();
                $ok = FALSE;
                $this->db->rollBack();
            } 
        }
        echo json_encode(array('ok' => $ok,'msg' => $msg));
    }
    
    public function notaredacaoAction() { 
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $msg = '';
        
        Zend_Loader::loadClass('tools','library');
        
        $ALU_ID = $this->_getParam('ALU_ID', 0);
        $PLS_ID = $this->_getParam('PLS_ID', 0);
        $PSA_REDNTA = $this->_getParam('PSA_REDNTA', 0);

        if($ALU_ID > 0 && $PLS_ID > 0){
            $this->db->beginTransaction();
            try{
                $data = array(
                    'PSA_REDNTA' => $PSA_REDNTA,
                );
                $ok = (bool) $this->db->update('PUBLICACAO_SIMULADO_ALUNO',$data,'PLS_ID = '.$PLS_ID.' AND ALU_ID = '.$ALU_ID);
                
                $this->db->commit();
                
                $ok = true;

            } catch(Exception $ex) {
                $msg = $ex->getMessage();
                $ok = FALSE;
                $this->db->rollBack();
            } 
        }
        echo json_encode(array('ok' => $ok,'msg' => $msg));
    }
    
    public function relatoriolistaAction(){
        
        $this->_helper->layout->disableLayout(); 
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $alu_id= $this->_getParam('id', 0);
        
        $this->view->ALU_ID = $alu_id;
        
        $query = 'SELECT SQL_CACHE p.*, a.* FROM  PUBLICACAO_LISTA p, PUBLICACAO_LISTA_ALUNO a '
                .' WHERE p.PUL_ID = a.PUL_ID AND a.ALU_ID = ' . $alu_id
                .' ORDER BY PUL_DATENC ASC, PUL_HORENC ASC';
        $data = $this->db->fetchAll($query);
        $this->view->result = $data;
        
        $query = "SELECT SQL_CACHE distinct(a.ALU_ID), a.ALU_NOM,c.CUR_ID
                    FROM ALUNO as a
                    INNER JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID AND m.MAT_STA = 1 
                    INNER JOIN CURSO as c ON m.CUR_ID = c.CUR_ID
                    WHERE a.ALU_ID = " . $alu_id;
        $this->view->aluno  = $this->db->fetchRow($query);
        
    }
    
    public function relatoriosimuladoAction(){
        
        $this->_helper->layout->disableLayout(); 
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $alu_id= $this->_getParam('id', 0);
        
        $this->view->ALU_ID = $alu_id;
        
        $query = 'SELECT SQL_CACHE p.*, a.* FROM  PUBLICACAO_SIMULADO p, PUBLICACAO_SIMULADO_ALUNO a '
                .' WHERE p.PLS_ID = a.PLS_ID AND a.ALU_ID = ' . $alu_id
                .' ORDER BY PLS_DAT ASC, PLS_HOR ASC';
        $data = $this->db->fetchAll($query);
        $this->view->result = $data;
        
        $query = "SELECT SQL_CACHE distinct(a.ALU_ID), a.ALU_NOM,c.CUR_ID
                    FROM ALUNO as a
                    INNER JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID AND m.MAT_STA = 1 
                    INNER JOIN CURSO as c ON m.CUR_ID = c.CUR_ID
                    WHERE a.ALU_ID = " . $alu_id;
        $this->view->aluno  = $this->db->fetchRow($query);
    }
}
